﻿using AutoMapper;
using FreeSqlDemo.Services;
using Magicodes.ExporterAndImporter.Core.Models;
using Magicodes.ExporterAndImporter.Excel;
using Magicodes.IE.Demo.Common;
using Magicodes.IE.Demo.Dto;
using Magicodes.IE.Demo.IService;
using Magicodes.IE.Demo.Modules;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using Shouldly;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace Magicodes.IE.Demo.Service
{
    public class ImporterService : IImporterService
    {
        private readonly IMapper _mapper;
        public ImporterService(IMapper mapper)
        {
            _mapper = mapper;
        }
        public static ExcelImporter Importer = new ExcelImporter();

        /// <summary>
        /// 导入学生数据
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public async Task<string> ImportStudentInfo(IFormFile file)
        {
            var stream = file.OpenReadStream();
            var import = await Importer.Import<ImportStudentDto>(stream);
            if (import.Data.Count() < 1)
                return "请导入有内容的表格";
            // 返回 导入异常信息
            if (import.Exception != null)
                return import.Exception.ToString();
            //异常错误
            if (import.RowErrors.Count > 0)
                return JsonConvert.SerializeObject(import.RowErrors);
            var studentList = new List<Student>();
            foreach (var item in import.Data)
            {
                var student = new Student()
                {
                    Name = item.Name,
                    Address = item.Address,
                    CampusId = item.CampusId,
                    ClassId = item.ClassId,
                    DormitoryNo = item.DormitoryNo,
                    Gender = item.Gender,
                    GradeId = item.GradeId,
                    Guardian = item.Guardian,
                    GuardianPhone = item.GuardianPhone,
                    HouseholdType = item.HouseholdType,
                    IdCard = item.IdCard,
                    IsBoarding = item.IsBoarding,
                    StudentNub = item.StudentNub,
                    MajorsId = item.MajorsId,
                    Nation = item.Nation,
                    Phone = item.Phone,
                    QQ = item.QQ,
                    Remark = item.Remark,
                    SchoolId = item.SchoolId,
                    SerialNumber = item.SerialNumber,
                    Status = item.Status,
                    StudentCode = item.StudentCode
                };
                studentList.Add(student);
            }
            //var list = GenFu.GenFu.ListOf<Student>(100); //随机生成数据
            var Count = FerrSQL.fsql.Insert(studentList).ExecuteAffrows();
            return $"导入成功,导入{Count}行";
        }

        /// <summary>
        /// 下载模板
        /// </summary>
        /// <returns></returns>
        public async Task<ActionResult> DownloadTemplate()
        {
            var result = await Importer.GenerateTemplateBytes<ImportStudentAndPaymentLogDto>();
            var fs = new MemoryStream(result);
            return new XlsxFileResult(stream: fs, fileDownloadName: "学生表与缴费表导入模板");
        }

        /// <summary>
        /// 多Sheet数据导入
        /// </summary>
        /// <returns></returns>
        public async Task<string> ImportStudentAndPaymentLog(IFormFile file)
        {
            var stream = file.OpenReadStream();
            var importDic = await Importer.ImportMultipleSheet<ImportStudentAndPaymentLogDto>(stream);

            //遍历字典，获取每个Sheet的数据
            foreach (var item in importDic)
            {
                var import = item.Value;
                
                //导入的Sheet数据，
                if (item.Key == "1班导入数据")
                {
                    if (import.Data.Count() < 1)
                        return "请导入有内容的表格";
                    // 返回 导入异常信息
                    if (import.Exception != null)
                        return import.Exception.ToString();
                    //异常错误
                    //if (import.RowErrors.Count > 0)
                    //    return JsonConvert.SerializeObject(import.RowErrors);
                    //List<ImportStudentDto> list =new List<ImportStudentDto>();

                    IList objList = (IList)import.Data;
                    int count = objList.Count; // The LINQ conversions will lose this information  
                    IEnumerable<ImportStudentDto> list = objList.Cast<ImportStudentDto>();
                    var studentsS= Students(list);

                    //var data = import.Data.ToList() ;
                    //var dto = (List<ImportStudentDto>)import.Data;
                    //var studentList = Students(import.Data);
                }
                if (item.Key == "缴费数据")
                {
                    ImportPaymentLogDto dto = (ImportPaymentLogDto)import.Data;
                }
            }


            //if (import.Data.Count() < 1)
            //    return "请导入有内容的表格";
            //// 返回 导入异常信息
            //if (import.Exception != null)
            //    return import.Exception.ToString();
            ////异常错误
            //if (import.RowErrors.Count > 0)
            //    return JsonConvert.SerializeObject(import.RowErrors);

            return null;
        }

        protected static List<Student> Students(IEnumerable<ImportStudentDto> Dto)
        {
            var studentList = new List<Student>();
            foreach (var item in Dto)
            {
                var student = new Student()
                {
                    Name = item.Name,
                    Address = item.Address,
                    CampusId = item.CampusId,
                    ClassId = item.ClassId,
                    DormitoryNo = item.DormitoryNo,
                    Gender = item.Gender,
                    GradeId = item.GradeId,
                    Guardian = item.Guardian,
                    GuardianPhone = item.GuardianPhone,
                    HouseholdType = item.HouseholdType,
                    IdCard = item.IdCard,
                    IsBoarding = item.IsBoarding,
                    StudentNub = item.StudentNub,
                    MajorsId = item.MajorsId,
                    Nation = item.Nation,
                    Phone = item.Phone,
                    QQ = item.QQ,
                    Remark = item.Remark,
                    SchoolId = item.SchoolId,
                    SerialNumber = item.SerialNumber,
                    Status = item.Status,
                    StudentCode = item.StudentCode
                };
                studentList.Add(student);
            }
            return studentList;
        }

    }
}
